package com.core.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import com.core.comment.ConfigUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class SqlHelper {

    private static String url = ConfigUtil.get("db.url");
    private static String username = ConfigUtil.get("db.username");
    private static String password = ConfigUtil.get("db.password");

    private static volatile DruidDataSource dataSource;



    static {
        if (dataSource == null) {
            init();
        }

    }

    public static void init() {
        dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
    }


    public static Object sqlOperate(String sql, Map<String, Object> par)  {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Object id = null;
        try {
             获得连接: con = getConnect();
            con.setAutoCommit(true);
            // 编写SQL：
            sql = SqlUtil.parseSql(sql, par);
            //System.out.println("处理后的sql:" + sql);
            pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            // 执行sql:
            pstmt.executeUpdate();
            rs = pstmt.getGeneratedKeys();

            if (rs.next()) {
                id = rs.getObject(1);

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return id;
    }

    public static Object sqlOperate(String sql) throws SQLException {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Object id = null;
        try {
            // 获得连接:
            con = getConnect();
            con.setAutoCommit(true);
            // 编写SQL：
            //System.out.println("处理后的sql:" + sql);
            pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            // 执行sql:
            pstmt.executeUpdate();
            rs = pstmt.getGeneratedKeys();

            if (rs.next()) {
                id = rs.getObject(1);

            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return id;
    }

    public static void sqlOperateList(List<String> sqls) throws SQLException {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Object id = null;
        try {
            con=getConnect();
            con.setAutoCommit(true);
            // 编写SQL：
            for(String sql:sqls){
                pstmt = con.prepareStatement(sql);
                // 执行sql:
                pstmt.executeUpdate();
            }


        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }

    }

    public static List<Map<String, Object>> queryMapList(String sql, Map<String, Object> par) throws SQLException {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            // 获得连接:
            con = getConnect();
            con.setAutoCommit(true);
            // 编写SQL：
            sql = SqlUtil.parseSql(sql, par);
            pstmt = con.prepareStatement(sql);
            // 执行sql:
            rs = pstmt.executeQuery();
            list = SqlUtil.handleResutSet(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return list;
    }



    public static Map<String, Object> queryMap(String sql, Map<String, Object> par) throws SQLException {
        List<Map<String, Object>> list = queryMapList(sql, par);
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


    /**
     * @return
     */
    public static Connection getConnect() throws SQLException {
        // 获得连接:
        Connection  conn = getDataSource().getConnection();
        return conn;

    }


    public static void setDataSource(DruidDataSource dataSource) {
        SqlHelper.dataSource = dataSource;
    }

    public static DruidDataSource getDataSource() {
        return dataSource;
    }
}
